﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ChequeManagement.DAL
{
    /// <summary>
    /// this class contain methods for connect to dataBase 
    /// this methods are Insert,Update,FetchAll,Delete,InitialObjectsFromReader,InitialBusinessObjectsFromReader
    /// this class inheritance of BaseDAL
    /// </summary>
    class DALDrawer : BaseDAL
    {
        /// <summary>
        /// this constructor called base constructor and set parameter
        /// parameter value is Connectionstring for connect to dataBase
        /// </summary>
        public DALDrawer()
            : base(ChequeManagement.Properties.Settings.Default.Database1ConnectionString)
        {
        }

        /// <summary>
        /// this method is for insert a object of BLL.BLLDrawer to table
        /// this method check connection if connection is open insert object to table
        /// </summary>
        /// <param name="drawerObject" Type="BLL.BLLDrawer"></param>
        /// <return>this type is void and Do not return</return>
        public void Insert(BLL.BLLDrawer drawerObject)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspDrawerInsert";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter drawerID = new SqlParameter();
                drawerID.ParameterName = "@drawerID";
                drawerID.SqlDbType = SqlDbType.Int;
                drawerID.Direction = ParameterDirection.Output;
                drawerID.IsNullable = false;
                drawerID.Value = drawerObject.DrawerID;
                this.Command.Parameters.Add(drawerID);

                SqlParameter drawerName = new SqlParameter();
                drawerName.ParameterName = "@drawerName";
                drawerName.SqlDbType = SqlDbType.NVarChar;
                drawerName.Direction = ParameterDirection.Input;
                drawerName.IsNullable = false;
                drawerName.Value = drawerObject.DrawerName;
                this.Command.Parameters.Add(drawerName);

                SqlParameter tel = new SqlParameter();
                tel.ParameterName = "@tel";
                tel.SqlDbType = SqlDbType.NVarChar;
                tel.Direction = ParameterDirection.Input;
                tel.IsNullable = false;
                tel.Value = drawerObject.Tel;
                this.Command.Parameters.Add(tel);

                SqlParameter address = new SqlParameter();
                address.ParameterName = "@address";
                address.SqlDbType = SqlDbType.NVarChar;
                address.Direction = ParameterDirection.Input;
                address.IsNullable = false;
                address.Value = drawerObject.Address;
                this.Command.Parameters.Add(address);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                drawerObject.DrawerID = Convert.ToInt32(this.Command.Parameters["@drawerID"].Value);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Update a object of BLL.BLLDrawer to table by drawerID
        /// this method check connection if connection is open Update object to table
        /// </summary>
        /// <param name="drawerObject" Type="BLL.BLLDrawer"></param>
        /// <param name="targetDrawerID" Type="int"></param>
        /// <return>this type is void and Do not return</return>
        public void Update(BLL.BLLDrawer drawerObject, int targetDrawerID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspDrawerUpdate";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter drawerID = new SqlParameter();
                drawerID.ParameterName = "@drawerID";
                drawerID.SqlDbType = SqlDbType.Int;
                drawerID.Direction = ParameterDirection.Input;
                drawerID.IsNullable = false;
                drawerID.Value = targetDrawerID;
                this.Command.Parameters.Add(drawerID);

                SqlParameter drawerName = new SqlParameter();
                drawerName.ParameterName = "@drawerName";
                drawerName.SqlDbType = SqlDbType.NVarChar;
                drawerName.Direction = ParameterDirection.Input;
                drawerName.IsNullable = false;
                drawerName.Value = drawerObject.DrawerName;
                this.Command.Parameters.Add(drawerName);

                SqlParameter tel = new SqlParameter();
                tel.ParameterName = "@tel";
                tel.SqlDbType = SqlDbType.NVarChar;
                tel.Direction = ParameterDirection.Input;
                tel.IsNullable = false;
                tel.Value = drawerObject.Tel;
                this.Command.Parameters.Add(tel);

                SqlParameter address = new SqlParameter();
                address.ParameterName = "@address";
                address.SqlDbType = SqlDbType.NVarChar;
                address.Direction = ParameterDirection.Input;
                address.IsNullable = false;
                address.Value = drawerObject.Address;
                this.Command.Parameters.Add(address);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="DrawerDT" Type="DataTable"></param>
        /// <return>this type is void but result return to DataTable </return>
        public void FetchAll(DataTable DrawerDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspDrawerFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                sqlDataAdapter.Fill(DrawerDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <returns>result return to a list<> of ChequeManagement.BLL.BLLDrawer</returns>
        public List<ChequeManagement.BLL.BLLDrawer> FetchAll()
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspDrawerFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {

                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();
                List<BLL.BLLDrawer> result = new List<BLL.BLLDrawer>();
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }
                if (controlConnection)
                    this.CommitTransaction();


                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();

                throw (ex);
            }

        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <returns>result return to a list<> of BLL.BLLDrawer</returns>
        public List<BLL.BLLDrawer> FetchByField(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspDrawerFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[Drawer].[" + fieldName + "] = N'" + value.ToString() + "'"));


                List<BLL.BLLDrawer> result = new List<BLL.BLLDrawer>();
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <param name="drawerDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchByField(string fieldName, object value, DataTable drawerDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspDrawerFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[Drawer].[" + fieldName + "] = N'" + value.ToString() + "'"));

                sqlDataAdapter.Fill(drawerDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by condition 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="condition" Type="string"></param>
        /// <param name="drawerDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchAllByCondition(string condition, DataTable drawerDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspDrawerFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", condition));

                sqlDataAdapter.Fill(drawerDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by DrawerID 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="bankID" Type="int"></param>
        /// <returns>return a object that type is BLL.BLLDrawer</returns>
        public BLL.BLLDrawer FetchByKey(int bankID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspDrawerFetchByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter drawerID = new SqlParameter();
                drawerID.ParameterName = "@drawerID";
                drawerID.SqlDbType = SqlDbType.Int;
                drawerID.Direction = ParameterDirection.Input;
                drawerID.IsNullable = false;
                drawerID.Value = bankID;
                this.Command.Parameters.Add(drawerID);


                if (controlConnection)
                    this.BeginTransaction();


                BLL.BLLDrawer drawerObject = new BLL.BLLDrawer();
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    if (dataReader.Read())
                        InitialBusinessObjectsFromReader(drawerObject, dataReader);
                    else
                        drawerObject = null;
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (drawerObject);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Delete a row from table
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="pkdrawerID" Type="int"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(int pkdrawerID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspDrawerDeleteByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter drawerID = new SqlParameter();
                drawerID.ParameterName = "@drawerID";
                drawerID.SqlDbType = SqlDbType.Int;
                drawerID.Direction = ParameterDirection.Input;
                drawerID.IsNullable = false;
                drawerID.Value = pkdrawerID;
                this.Command.Parameters.Add(drawerID);



                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Delete a row from table by field name that field name value is this value
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspDrawerDeleteByField";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                this.Command.Parameters.Add(new SqlParameter("@fieldName", fieldName));
                this.Command.Parameters.Add(new SqlParameter("@value", value));

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>return a list<> that value is table columns value</returns>
        private List<BLL.BLLDrawer> InitialObjectsFromReader(IDataReader dataReader)
        {
            List<BLL.BLLDrawer> drawerList = new List<BLL.BLLDrawer>();

            int drawerID = dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.DrawerID.ToString());
            int drawerName = dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.DrawerName.ToString());
            int tel = dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.Tel.ToString());
            int address = dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.Address.ToString());

            while (dataReader.Read())
            {

                BLL.BLLDrawer drawerObject = new BLL.BLLDrawer();

                drawerObject.DrawerID = dataReader.GetInt32(drawerID);
                drawerObject.DrawerName = dataReader.GetString(drawerName);
                drawerObject.Tel = dataReader.GetString(tel);
                drawerObject.Address = dataReader.GetString(address);
                drawerList.Add(drawerObject);
            }
            return (drawerList);
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="drawerObject" Type="BLL.BLLDrawer"></param>
        /// <param name="dataReader" Type="IDataReader"></param>
        private void InitialBusinessObjectsFromReader(BLL.BLLDrawer drawerObject, IDataReader dataReader)
        {
            drawerObject.DrawerID = dataReader.GetInt32(dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.DrawerID.ToString()));
            drawerObject.DrawerName = dataReader.GetString(dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.DrawerName.ToString()));
            drawerObject.Tel = dataReader.GetString(dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.Tel.ToString()));
            drawerObject.Address = dataReader.GetString(dataReader.GetOrdinal(BLL.BLLDrawer.DrawerField.Address.ToString()));

        }
    }
}
